'''
操作sqlite数据库的模块，用于把爬取得到的帖子内容存入数据库
'''

import json
import sqlite3
from typing import Optional, Iterable, List

from tiebaparser import TiebaContent, FloorContent, ReplyContent


DEFAULT_PATH = "data/tieba.db"
UPDATE_DB = False


class TiebaDB:
    '''用于将解析得到的页面内容存入数据库'''

    def __init__(self, fname=DEFAULT_PATH, autocommit=False, update=UPDATE_DB):
        self.fname = fname
        self.autocommit = autocommit
        self.update = update
        self._conn = None    # type: Optional[sqlite3.Connection]
        self._is_table_created = False    # 数据库中的表是否已创建

        self._register_adapters()

    @staticmethod
    def _register_adapters():
        '''注册转换列表的适配器'''
        sqlite3.register_adapter(list, lambda l: json.dumps(l))

    def connect(self, *args, **kwargs):
        '''连接sqlite数据库'''
        if self._conn is None:
            self._conn = sqlite3.connect(self.fname, *args, **kwargs)
            self._conn.row_factory = sqlite3.Row
        return self._conn

    def cursor(self, factory=sqlite3.Cursor):
        '''返回一个sqlite数据库的游标'''
        return self._conn.cursor(factory)

    def _create_tables(self, cur: sqlite3.Cursor):
        '''创建存储楼层内容、楼中楼内容的表'''
        if not self._is_table_created:
            cur.execute('''
                CREATE TABLE IF NOT EXISTS floor_contents (
                    floor_id INTEGER PRIMARY KEY,
                    floor INT NOT NULL,
                    text TEXT NOT NULL,
                    time TEXT NOT NULL,
                    user TEXT NOT NULL,
                    total_replys INT NOT NULL
                )
            ''')
            cur.execute('''
                CREATE TABLE IF NOT EXISTS reply_contents (
                    page INT NOT NULL,
                    ord INT,    -- 表示这个回复是这一页的第几条
                    text TEXT NOT NULL,
                    time TEXT NOT NULL,
                    user TEXT NOT NULL,
                    floor_id INT NOT NULL,
                    FOREIGN KEY (floor_id) REFERENCES floor_contents(floor_id),
                    UNIQUE (floor_id, page, ord)
                )
            ''')
            self._is_table_created = True

    _floor_keys = ("floor_id", "floor", "text", "time", "user", "total_replys")

    _reply_keys = ("page", "ord", "text", "time", "user", "floor_id")

    _write_sql = '''
        INSERT OR IGNORE INTO {0} ({1}, {2}, {3}, {4}, {5}, {6})
        VALUES (:{1}, :{2}, :{3}, :{4}, :{5}, :{6})
    '''

    _write_floor_sql = _write_sql.format("floor_contents", *_floor_keys)

    _write_reply_sql = _write_sql.format("reply_contents", *_reply_keys)

    def write(self, content: TiebaContent):
        '''将楼层或楼中楼内容写入数据库'''
        cur = self._conn.cursor()
        self._create_tables(cur)

        if isinstance(content, FloorContent):
            keys = self._floor_keys
            sql = self._write_floor_sql
        elif isinstance(content, ReplyContent):
            keys = self._reply_keys
            sql = self._write_reply_sql
        else:
            raise TypeError("`content` requires an instance of `TiebaContent`")

        if self.update:
            sql = sql.replace("IGNORE", "REPLACE")
        cur.execute(sql, {k: getattr(content, k, None) for k in keys})
        if self.autocommit: 
            self._conn.commit()
        return cur

    def writemany(self, contents: List[TiebaContent]):
        '''使用executemany方法写入数据库，以提高性能'''
        cur = self._conn.cursor()
        self._create_tables(cur)

        keys = ()    # 解决pycharm报错
        sql = ""    # 解决pycharm报错
        if not isinstance(contents, Iterable):
            cls = type(contents).__name__
            raise TypeError(f"`{cls}` object is not iterable")
        elif not len(contents):
            return cur
        elif isinstance(contents[0], FloorContent):
            keys = self._floor_keys
            sql = self._write_floor_sql
        elif isinstance(contents[0], ReplyContent):
            keys = self._reply_keys
            sql = self._write_reply_sql

        if self.update:
            sql = sql.replace("IGNORE", "REPLACE")
        params_seq = ({k: getattr(content, k, None) for k in keys}
                      for content in contents)
        cur.executemany(sql, params_seq)
        if self.autocommit: 
            self._conn.commit()
        return cur

    def read(self, floor, reply=None):
        '''从数据库中读取帖子内容'''
        pass

    def close(self):
        self._conn.close()

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()


def open_tiebadb(database=DEFAULT_PATH, autocommit=False,
                 update=UPDATE_DB, *args, **kwargs):
    tiebadb = TiebaDB(database, autocommit=autocommit, update=update)
    tiebadb.connect(*args, **kwargs)
    return tiebadb


if __name__ == "__main__":
    pass
